sorry for another question but I'm not good with databases.
I'm developing a small application on Lazarus (Delphi like) and my application is just a couple of SQL statements, inside of my database I have this similar structure for phone storage.
tblUser uid uname udateofbirth uadress
tblPhoneNumber pid uid ptype pnumber
tblPhoneRelation uid pid
What I need to know is if SQL can provide a some kind of list when I'm doing a query with the phones, so I can split this list inside some fields.
With this code from another question, I can retrieve the 'max' number of each type of phone type, but the point is that I can have more than one number of the same kind, like 2 cell phone numbers, or 3 home numbers for example.
SELECT u.uname AS Name, u.udateofbirth AS 'Date of B.',
MAX(CASE WHEN p.ptype = 1 THEN p.pnumber END) AS 'Cell phone',
MAX(CASE WHEN p.ptype = 2 THEN p.pnumber END) AS 'Home phone',
MAX(CASE WHEN p.ptype = 3 THEN p.pnumber END) AS 'Commercial phone',
MAX(CASE WHEN p.ptype = 4 THEN p.pnumber END) AS 'Message phone'
FROM tblUser AS u
JOIN phonerelation ON u.uid = phonerelation.uid
JOIN tblPhoneNumber AS p ON p.pid = phonerelation.pid
GROUP BY 1, 2
Any ideas inside SQL or need to create a for-while code inside of Lazarus retrieving each kind of phone at once? Like: try to find cell phones ... if cell phone count is < 4 try to find home phone... until I get some results.
Ty for the help.
Hope this will give you some ideas:
SELECT u.uname AS Name, u.udateofbirth AS 'Date of B.',
p1.pnumber AS 'Cell phone',
p2.pnumber AS 'Home phone',
...
FROM tblUser AS u
LEFT JOIN (
select pnumber, uid
from phonerelation JOIN
tblPhoneNumber AS p ON p.pid = phonerelation.pid WHERE p.ptype = 1) p1
ON u.uid = p1.uid
LEFT JOIN (
select pnumber, uid
from phonerelation JOIN
tblPhoneNumber AS p ON p.pid = phonerelation.pid WHERE p.ptype = 2) p2
ON u.uid = p2.uid
...
-- Grouping is not needed here