Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT * FROM users user
- LEFT JOIN emails email ON email.user_id=user.id
- LEFT JOIN phones phone ON phone.user_id=user.id
- [
- {
- "name": "John Smith",
- "dob": "1945-05-13",
- "fav_color": "red",
- "email": "johnsmith45@gmail.com",
- },
- {
- "name": "John Smith",
- "dob": "1945-05-13",
- "fav_color": "red",
- "email": "john@smithsunite.com",
- },
- {
- "name": "Jane Doe",
- "dob": "1953-02-19",
- "fav_color": "green",
- "email": "originaljane@deerclan.com",
- }
- ]
- [
- {
- "name": "John Smith",
- "dob": "1945-05-13",
- "fav_color": "red",
- "emails": ["johnsmith45@gmail.com", "john@smithsunite.com"]
- },
- {
- "name": "Jane Doe",
- "dob": "1953-02-19",
- "fav_color": "green",
- "emails": ["originaljane@deerclan.com"],
- }
- ]
- SELECT * FROM users user where [criteria here]
- SELECT * from EMAILS where user_id in (list of IDs here)
- SELECT * from PHONES where user_id in (list of IDs here)
- SELECT user.*,
- (SELECT GROUP_CONCAT(DISTINCT emailAddy) FROM emails email WHERE email.user_id = user.id
- ) AS EmailAddresses,
- (SELECT GROUP_CONCAT(DISTINCT phoneNumber) FROM phones phone WHERE phone.user_id = user.id
- ) AS PhoneNumbers
- FROM users user
- username department EmailAddresses PhoneNumbers
- Tim_Burton Human Resources hr@m.com, tb@me.com, nunya@what.com 231-123-1234, 231-123-1235
- Select * from...
- select users.name, emails.email_address, phones.home_phone, phones.bus_phone
- from...
- select distinct users.name, emails.email_address, phones.home_phone, phones.bus_phone
- from...
- select distinct users.name, users.id, emails.email_address, phones.phone_number
- from users
- inner join emails on users.user_id = emails.user_id
- inner join phones on users.user_id = phones.user_id
- select * from Phones where user_id=344;
- ;with toList as (
- select *, Stuff(( select ',' + (phone.phoneType + ':' + phone.PhoneNumber)
- from phones phone
- where phone.user_id = user.user_id
- for xml path('')
- ), 1,1,'') as phoneNumbers
- from users user
- )
- select *
- from toList
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement