Help about MySQL Query

1

Hi guys

I have two tables;
users:

Vehicle1
Vehicle2
Vehicle3
Vehicle4
Vehicle5

vehicle:
ID

(I’m just give you information which you might need…)

In Vehicle1,Vehicle2… i store ID of vehicles.Now,i want to delete all vehicles which are not on any slot(Vehicle1,Vehice2…) from table users.

So.I need to selet all ID from user table
SELECT Vehicle1,Vehicle2,Vehicle3,Vehicle4,Vehicle5 FROM users

And now delete all ID from vehicle which are not on any slot.I hope you can understand me,sorry for my English.

Thanks

2

I’m found way to SELECT all Vehicles which are on player slot…But I need to delete vehicles which are not on any slot…

SELECT bu.ID FROM vehicle bu inner join users u where u.Vehicle1 = bu.ID || u.Vehicle2 = bu.ID || u.Vehicle3 = bu.ID || u.Vehicle4 = bu.ID || u.Vehicle5 = bu.ID

4

Yes,Vehicle1,Vehicle2… are field names in users table.

There I store ID of Vehicles

So I need to delete all Vehicle ID which don’t exist in any field name from users table(Vehicle1,Vehicle2…)

Can you undestand now?

6

Yes,i want to say it.But my English is bad,sorry.

8

Something is not good.
I have 8 vehicles after it query(I can’t have 8 if there have just 5 slots…)

10

Slot =
Vehicle1
Vehicle2
Vehicle3
Vehicle4
Vehicle5

So there have 5 slots,right?I mean on it…

For example for my user account:
Vehicle1 = 10
Vehicle2 = 33
Vehicle3 = 45
Vehicle4 = 55
Vehicle5 = 66

In Vehicle Table

ID = 10 , Price= 50$
ID = 33 , Price = 85$
ID = 45 , Price 43 $
ID = 55, Price 31 $
ID = 66 , Price 303 $
ID = 109 ,Price 4420 $ etc…

As you can see,all ID(From user table) expect 109 exist.So I want to delete 109 ID from vehicle table…

12

vehicle table: yes
User table: second :slight_smile:

14

It’s not field.I’m just do this:
SELECT COUNT(ID) as vehiclenumber FROM vehicle WHERE name = 'PaulC'

I’m count because after query for delete i can’t have more than 5 vehicles.

16

Of course,i know it and I want it :slight_smile:

This Count is from vehicle table :slight_smile:
But It don’t work…

Do you agree with me:
If I have
Vehicle1
Vehicle2
Vehicle3
Vehicle4
Vehicle5

And If I make query good(That delete all ID which are not on any of this slots(vehicle1,vehicle2…) that I can’t have more than 5 vehicles? There is problem,after query;i have 8 vehicles.

After your query:
SELECT ID FROM vehicle WHERE name = 'PaulC'
Result:

ID

  • 15
  • 190
  • 378
  • 21
  • 22
  • 99
  • 110
  • 126

SELECT Vehicel1,Vehicel2,Vehicel3,Vehicel4,Vehicle5 FROM users WHERE name = 'Paul_Castellano'

Result:

  • Vehicle1: 22
  • Vehicle2: 99
  • Vehicle3 : 110
  • Vehicle4: 126
  • Vehicle5: 0(I don’t have Vehicle5)

So…
ID 15,190,378,21 are not on any slot(vehicle1,vehicle2…) and they are not deleted…I don’t know why.

EDIT:

"You did not say you wanted to delete vehicle IDs for a specify user. "

I want to delete for all users.I’m just use my account for examples!

17

When you ran the SQL code I gave you earlier, did it not show an error? I noticed you had a capital V instead of a lowercase v for the 5 fields in users. I have corrected my original query to be a capital V for each field name, so this should work. I recreated the following tables:

users table
userID Vehicle1 Vehicle2 Vehicle3 Vehicle4 Vehicle5
1 22 99 110 126 0

vehicle table
ID
15
190
378
21
22
99
110
126

After the following SQL exectutes, IDs 15, 190, 378, 21 will be deleted from the vehicle table, leaving only IDs 22, 99, 110, 126

DELETE FROM vehicle where ID NOT IN (
  SELECT DISTINCT Vehicle1 FROM users
  UNION SELECT Vehicle2 FROM users
  UNION SELECT Vehicle3 FROM users
  UNION SELECT Vehicle4 FROM users
  UNION SELECT Vehicle5 FROM users
);
1 Like
18

I’m so sorry!
It’s my bad.
Now I’m check and ID: 15,190,378,21 have on another user account(This is my test database so…Some things are mixed…)
Sorry for your time,I’m also lost my day with this…Query is good and it will work on my main database.