Any SQL people here?

I have a query that joins onto another table. This results in mulitple rows of data. Thing is, in this instance, I want only one row returned with extra columns to show the extra joined rows.

e.g. 2 tables (person table, phone table)
Current results is (assume name is from person table and type & number is from phone table)
Name Type Number
Paul home 01212232323
Paul mobile 07777123456
Paul work 01212232323
Bob home 0209121212
Bob mobile 07070707070

My desired result is one row
Name home_no mobile_no work_no
Paul 01212232323 07777123456 01212232323
Bob 02091212121 07071717171 none

etc..

I can do it as a query with 3 joins onto the phone table. I'm sure there's a simpler way to do it (with one join) but can't figure it out.

Anyone? Thanks in advance.

 

You'll have to use cursor's (assuming you're using MS SQL Server) or something equivalent for your SQL Version.

Basically Create a Cursor containing your SELECT query of the Peoples table then run through them one by one (just like a do while loop), creating another cursor from the Phone table for each person. Using the Phone cursor (which you can refine in the SELECT statement) run through the Phone records and create a string using concatenation. When you've run through the Phone records and you are at the end of the cursor write the whole lot out using a SELECT statement, fetch the next person and start again until the you've run through all the Person records.

Easier to do than describe, if you have MS SQL let me know and I'll send you some Transact SQL that you can adapt to your table / column names.

 

This comment is no help , but Drac, I feel your pain. I am having major troubles with a MySQL - PHP db at the moment.

Anyway, back to it

 

Thanks. Ideally I'm wanting to do a SQL platform independant code. Currently on DB2 but want to use the syntax in MySQL, Oracle etc. so pl/sql, cursors etc. aren't ideal (although I appreciate the suggestion )

I can write 3 joins to the phone table (each with their condition being home for join 1, mobile for join 2 etc.) and that will work.

I was just certain that this could be done with one join using the standard select statement, maybe I was wrong :banghead: My real code involves about 16 tables, many conditions, temporary tables etc. so the mulitple join route, although workable, is a bit of a bu66er to code.

My current 'solution'...

SELECT p.name, h.home_no, m.mobile_no, w.work_no
FROM person p
LEFT OUTER JOIN phone h
ON p.person_id = h.person_id
AND h.type = 'home'
LEFT OUTER JOIN phone m
ON p.person_id = h.person_id
AND h.type = 'mobile'
LEFT OUTER JOIN phone w
ON p.person_id = h.person_id
AND h.type = 'work'

 

What is common between the tables? the NAME field or a separate ID field?
I'm confused between your two posts, your first says the second table has a TYPE field and a NUMBER field, but in your second you show home_no field, mobile_no field and work_no field? and it looks like it's joining the NAME table with 3 other tables?
Please clarify?
If it was just 2 tables like your original what's wrong with a simple subselect?
select name1.*,
(select number1.number from number1 where type = 'home' and number1.idf = name1.idf) as Home1,
(select number1.number from number1 where type = 'work' and number1.idf = name1.idf) as work1,
(select number1.number from number1 where type = 'Mobile' and number1.idf = name1.idf) as Mobile1
from name1

This will output the following...
IDf Number Home1 work1 Mobile
----------- ---------- --------------- --------------- ---------------
1 T 12345687 121212 333333
2 C 1111 3333333 NULL
3 S NULL NULL 22222

Joins? Whats them then

 

Remember MySQL doesn't do sub-selects, so if you want a pure SQL99 or whatever solution, it may well be a bit wordy