[SOLVED] SQL newbie query

[nick2price]

I want to use the select function but it is quite hard to explain but i will try. I have created several tables and added all needed constraints on them.

I want to do a querry where i select all events that hire both large and small items. To do this i need to do somthing like

 

select events from hireSmall and hireLarge

 

I know thats not right but it gives u an idea. Then The eventNo in The events table is linked with a customer number in the Customer table. So in all, i need to return the event number and customer number. In all, my SQL should look somthing like:

 

select events (that are in both) from hireSmall and hireLarge, use this events number to get customer details from customer tabel

 

Any help?

 

I have started doing somthing like this

 

SELECT *
FROM
(
SELECT Events from Hiring_Small_Items
UNION ALL
SELECT Events from Hiring_Large_Items
)

 

So that should get all Events which hire both large and small items and put them in 1 column. Now i want to put this into a new table. SO if i create a new table,

CREATE TABLE AllEvents

 

How do i put the above into it. I know u normally use insert but i havnt named the above.

p.s. i am not sure if my code is correct

 

[nick2price]

Any Advice?

[fenway]

Why not just a simple UNION?

[nick2price]

I have used union above.  The way things stands, what i have above returns all the events that are in both the Hire_Large_Item table and Hire_Small_Item table.  I only want returned the events that are in both, not one or the other.  I think i should be using AND somewhere but not too sure where.

[nick2price]

I am trying to do somthing like this now but it is not working at present, but hopefully u see what i am attempting;

 

SELECT *
FROM
(
SELECT EventNo from Hiring_Small_Items
UNION ALL
SELECT EventNo from Hiring_Large_Items
WHERE
EventNo in (Hiring_Small_Items AND Hiring_Large_Items)
)

[Barand]

SELECT s.EventNo
FROM Hiring_Small_Items s
    INNER JOIN Hiring_Large_Items l 
    ON s.EventNo = l.EventNo

[nick2price]

Could you explain that a bit please.  I got it working by doing this

SELECT *
FROM
(
SELECT EventNo from Hiring_Small_Items
INTERSECT
SELECT EventNo from Hiring_Large_Items
)

 

Does that do the same job as yours?  I am not sure what the s. and 1. do in your example

[Barand]

My query selects those eventNos in Hiring_Small_Items that have a match in Hiring_Large_Items.

 

s and l are table aliases - saves repeating the table names. You could write the same thing without the aliases

 

SELECT Hiring_Small_Items.EventNo

FROM Hiring_Small_Items

    INNER JOIN Hiring_Large_Items

    ON Hiring_Small_Items.EventNo = Hiring_Large_Items.EventNo

[nick2price]

I like your way better than using an intersect, but is there anyway to make your way not repeat the Event Number. For example, intersect returns

E01

E02

E03

E04

E05

 

Your way returns

E01

E01

E01

E02

E02

E03

E03

E03 etc

[fenway]

Add a "GROUP BY Hiring_Small_Items.EventNo"

[nick2price]

So these are my tables

 

Hiring_Small_Items

HNO ENO Stockno QTY

L01 E01 Sto7 50

L02 E01 St08 200

L03 E01 St09 1

L04 E02 Sto7 100

L05 E03 Sto7 60

L06 E03 St08 240

 

Hiring_Small_Items

HNO ENO Stockno QTY

S01 E01 St01 200

S02 E01 Sto2 200

S03 E01 St03 200

S04 E01 St04 200

S05 E01 St05 200

S06 E02 St01 400

S07 E03 St01 240

S08 E03 Sto2 240

 

This sql statement returns all event numbers that appears in both the Hiring_Small_Items table and Hiring_Large_Items table.

 

SELECT Hiring_Small_Items.EventNo
FROM Hiring_Small_Items 
    INNER JOIN Hiring_Large_Items 
    ON Hiring_Small_Items.EventNo = Hiring_Large_Items.EventNo 
GROUP BY Hiring_Small_Items.EventNo
ORDER BY Hiring_Small_Items.EventNo

 

Now i have another table called Events which has an event number in and a customer number.

 

EVENTS

ENO CNO EDATE           LOCATION

E03 C03 06-FEB-08 locE03

E04 C04 03-FEB-08 locE04

E06 C02 01-FEB-08 locE06

E05 C06 04-FEB-08 locE05

E07 C05 03-FEB-08 locE07

E02 C08 10-FEB-08 locE02

 

Can i continue the sql code above to return me the customer number linked with the event numbers provided by the sql statement above?

[fenway]

If you mean can you join in another table, then yes... and FYI, group by does an implicit order by.

[nick2price]

i am not too sure what you mean by implicit, but with just group by there, the numbers didnt come up in order, they do now though with the order by statement

[Barand]

What database are you using (I haven't come across INTERSECT in MySQL)?

[nick2price]

People always ask me that question and i always seem to give the wrong answer.  I am using Oracle 10g Express Edition as i was told to use.  Everything is done through a sort of webpage so i dont work in a command window.  So i dont think this uses anything like mySQL etc. or if it does, its behind the scenes.

 

You wouldnt by any chance be able to give me an example of how to continue my query to do what i stated a couple of post up.  I am completely lost with how to continue it and knowing this will enable me to get on with my other quiries by myself.

Cheers

[Barand]

Oracle - that would explain the group by/order by thing - in MySQL the order is implicit in the group by

 

SELECT DISTINCT S.EventNo, E.cno
FROM Hiring_Small_Items S
    INNER JOIN Hiring_Large_Items L
        ON S.EventNo = L.EventNo
    INNER JOIN Events E
        ON S.EventNo = E.EventNo 
ORDER BY S.EventNo

[nick2price]

kool, That works great.  Just another quick question, If i want to further this again to use the CNo to then get the Customer Name from a customers table, i have tried adding to your code by doing this

SELECT DISTINCT S.EventNo, E.cno, C.CName
FROM Hiring_Small_Items S
    INNER JOIN Hiring_Large_Items L
        ON S.EventNo = L.EventNo
    INNER JOIN Events E
        ON S.EventNo = E.EventNo 
    INNER JOIN Events C
        ON S.EventNo = C.CName
ORDER BY S.EventNo

 

I am being returned with an error saying ORA-00904: "C"."CNAME": invalid identifier.  I have tried different letters but still the same.  What is it that i am doing wrong?

 

PS my customer table is

CNO CNAME CADDRESS

C01 John Caddress01

C02 Fred Caddress02

C03 Amy Caddress03

C04 Ian Caddress04

C05 Kim Caddress05

C06 Steve Caddress06

C07 Susan Caddress07

C08 Jane Caddress08

 

[Barand]

don't know struct of the customer table but something like

SELECT DISTINCT S.EventNo, E.cno, C.CName
FROM Hiring_Small_Items S
    INNER JOIN Hiring_Large_Items L
        ON S.EventNo = L.EventNo
    INNER JOIN Events E
        ON S.EventNo = E.EventNo 
    INNER JOIN Customers C
        ON E.Cno = C.Cno
ORDER BY S.EventNo