SQL Query Help
-
how can i select multiple "u.slot_number" i have attached the image below and the query that i am running, for better description
SELECT
p.employee,
p.first_name,
p.last_name,
d.description,
b.expired_date,
u.description
FROM person p
left join badge b on b.person_id = p.id
left join department d on d.id = p.department
left join person_user u on u.person_id =p.id AND u.slot_number=1
WHERE p.type = 3
ORDER BY p.employee
-
assuming this is what you mean you can change the = to an IN. so something like this.
SELECT
p.employee,
p.first_name,
p.last_name,
d.description,
b.expired_date,
u.description
FROM person p
left join badge b on b.person_id = p.id
left join department d on d.id = p.department
left join person_user u on u.person_id =p.id AND u.slot_number IN(1,2,3,4)
WHERE p.type = 3
ORDER BY p.employee
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
-
My query is giving this output:
SELECT
p.employee,
p.first_name,
p.last_name,
d.description,
b.expired_date,
u.description
FROM person p
left join badge b on b.person_id = p.id
left join department d on d.id = p.department
left join person_user u on u.person_id =p.id AND u.slot_number=1
WHERE p.type = 3
ORDER BY p.employee
Dan.Humphries (4/20/2011)
assuming this is what you mean you can change the = to an IN. so something like this.SELECT
p.employee,
p.first_name,
p.last_name,
d.description,
b.expired_date,
u.description
FROM person p
left join badge b on b.person_id = p.id
left join department d on d.id = p.department
left join person_user u on u.person_id =p.id AND u.slot_number IN(1,2,3,4)
WHERE p.type = 3
ORDER BY p.employee
will this query give this output?
sorry for being noobish i just need to clarify as there is not internet in the place that i will run this query.
-
Nope that would require something a bit more ocmplicated. Before we could begin to answer that though could you explain how the information is stored in person_user. What is the difference between User1, User2 and User3
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
-
I will try to explain it in the best i can as i am not on the site, basically the person_user are user field ex. user1 user2 user3 and u.description is the the actual data that is written in the user field, and u.slot_number is the actual user field ex. user1 user2 user3
I just phoned my buddy but i cant really understand what he meant, but this is what he said i hope you can understand it
"you need to select description multiple times, alias it and create separate conditional statement for each."
how do i apply that in my query? or will that even work, i dont even know if he gets what i meant :ermm:
-
This should do the trick.
SELECTp.employee,
p.first_name,
p.last_name,
d.description,
b.expired_date,
u1.description,
u2.description,
u3.description
FROM
person p
left join badge b on b.person_id = p.id
left join department d on d.id = p.department
left join person_user u1 on u.person_id =p.id AND u.slot_number =1
left join person_user u2 on u.person_id =p.id AND u.slot_number =2
left join person_user u3 on u.person_id =p.id AND u.slot_number =3
WHERE p.type = 3
ORDER BY p.employee
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
-
thank you very much, i will try it out on the site, hopefully it works.
-
It will work, but this isn't a great way to store and query this data. You ought to look at refactoring this over time and storing those "slots" in another table.
-
Dan.Humphries (4/20/2011)
This should do the trick.SELECTp.employee,
p.first_name,
p.last_name,
d.description,
b.expired_date,
u1.description,
u2.description,
u3.description
FROM
person p
left join badge b on b.person_id = p.id
left join department d on d.id = p.department
left join person_user u1 on u.person_id =p.id AND u.slot_number =1
left join person_user u2 on u.person_id =p.id AND u.slot_number =2
left join person_user u3 on u.person_id =p.id AND u.slot_number =3
WHERE p.type = 3
ORDER BY p.employee
I tried running the query but it does not work.
-
what does it do?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
-
the system got stuck and i waited for around 5 mins then tried it again same thing.
-
I just realised I messed up the alias in the joins. did you happen to correct those? If not I am rather surpriesed it did not give you an error right off the bat. how many records would you say we are dealing with?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
-
nope i did not correct them, i just put what you have given me, as you know i am real noob in SQL, what is the correct alias btw? and i also did some research may i know what is the difference between left join and full join? its really difficult not to have internet access on the site.
-
using The buzz word of the day from yesterday we shall use this as a mentoring moment ya.
so if you look at my joins you will see person_user u1. The alias part is the "u1" so now everytim you want to refer to this table you can refer to it as u1 rather than person_user. So in the full join the alias has to match the case. since we are referening the same table multiple times we need to make sure we get out alias references to line up. I forgot to update the alias in the on clause and so they all reflect u. It should be.
left join person_user u1 on u1.person_id =p.id AND u1.slot_number =1left join person_user u2 on u2.person_id =p.id AND u2.slot_number =2
left join person_user u3 on u3.person_id =p.id AND u3.slot_number =3
notice how the alias of table precedes the column name in each one.
As for the joins well my advice is google and read through the many examples. There is really alot more to it than I can explain in the space here.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
-
as per your input above in if there is a data in table 2 if table 1 and table 3 is empty do i need to make it full join? or just left join?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply